package com.yidu.t299.dao.xp;
import com.yidu.t299.domain.xp.Class;
import com.yidu.t299.domain.xp.Record;
import com.yidu.t299.domain.xp.School;
import com.yidu.t299.domain.xp.StudentManage;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import java.util.List;

/*
使用Mybatis逆向生成自动生成 后续自己更改
 */
@Mapper
public interface StudentManagebMapper {
    /**
     *根据学员编号删除该学员信息
     * @param studentId 学员编号
     * @return 影响的行数(返回1则成功)
     */
    @Delete({
        "delete from student_manage",
        " where student_id = #{studentId,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer studentId);

    /**
     *增加学员信息
     * @param record 学员对象
     * @return 影响的行数(返回1则成功)
     */
    @Insert({
        "insert into student_manage (student_id, student_name, ",
        "student_age, student_sex, ",
        "student_stat, student_address, ",
        "student_qq, student_phone, ",
        "student_date, class_id, ",
        "school_id, record_id)",
        "values (#{studentId,jdbcType=INTEGER}, #{studentName,jdbcType=VARCHAR}, ",
        "#{studentAge,jdbcType=INTEGER}, #{studentSex,jdbcType=VARCHAR}, ",
        "#{studentStat,jdbcType=VARCHAR}, #{studentAddress,jdbcType=VARCHAR}, ",
        "#{studentQq,jdbcType=VARCHAR}, #{studentPhone,jdbcType=VARCHAR}, ",
        "#{studentDate,jdbcType=VARCHAR}, #{classId,jdbcType=INTEGER}, ",
        "#{schoolId,jdbcType=INTEGER}, #{recordId,jdbcType=INTEGER})"
    })
    int insert(StudentManage record);

    /**
     *根据学员编号查询指定的信息
     * @param studentId 学员编号
     * @return
     */
    @Select({
        "select",
        "student_id, student_name, student_age, student_sex, student_stat, student_address, ",
        "student_qq, student_phone, student_date, class_id, school_id, record_id",
        "from student_manage",
        "where student_id = #{studentId,jdbcType=INTEGER}"
    })
    @Results(id = "StudentMap",value = {
        @Result(column="student_id", property="studentId", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="student_name", property="studentName", jdbcType=JdbcType.VARCHAR),
        @Result(column="student_age", property="studentAge", jdbcType=JdbcType.INTEGER),
        @Result(column="student_sex", property="studentSex", jdbcType=JdbcType.VARCHAR),
        @Result(column="student_stat", property="studentStat", jdbcType=JdbcType.VARCHAR),
        @Result(column="student_address", property="studentAddress", jdbcType=JdbcType.VARCHAR),
        @Result(column="student_qq", property="studentQq", jdbcType=JdbcType.VARCHAR),
        @Result(column="student_phone", property="studentPhone", jdbcType=JdbcType.VARCHAR),
        @Result(column="student_date", property="studentDate", jdbcType=JdbcType.VARCHAR),
        @Result(column="class_id", property="classId", jdbcType=JdbcType.INTEGER),
        @Result(column="school_id", property="schoolId", jdbcType=JdbcType.INTEGER),
        @Result(column="record_id", property="recordId", jdbcType=JdbcType.INTEGER)
    })
    StudentManage selectByPrimaryKey(Integer studentId);

    /**
     *按条件分页查询学员数据集合
     * @param rows 每页的行数
     * @param page 页码
     * @param condition 条件参数
     * @return 转班对象集合
     */
    @Select("select * from student_manage cm " +
            "join class c " +
            "on cm.class_id=c.class_id " +
            "join school sm " +
            "on c.school_id=sm.school_id " +
            "join record re " +
            "on re.record_id=cm.record_id  where 1=1 ${condition} limit #{page},#{rows}")
    @Results({
            @Result(column = "student_id",property = "studentId",jdbcType = JdbcType.INTEGER, id = true),
            @Result(column = "student_name",property ="studentName" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "student_age",property ="studentAge" ,jdbcType = JdbcType.INTEGER),
            @Result(column = "student_sex",property ="studentSex" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "student_stat",property ="studentStat" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "student_address",property ="studentAddress" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "student_qq",property ="studentQq" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "student_phone",property ="studentPhone" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "student_date",property ="studentDate" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "class_id",property ="classId" ,jdbcType = JdbcType.INTEGER),
            @Result(column = "school_id",property ="schoolId" ,jdbcType = JdbcType.INTEGER),
            @Result(column = "record_id",property ="recordId" ,jdbcType = JdbcType.INTEGER),
            @Result(column = "school_name",property ="schoolName" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "Class_name",property ="ClassName" ,jdbcType = JdbcType.VARCHAR),
            @Result(column = "record_name",property ="recordName" ,jdbcType = JdbcType.VARCHAR)
    })
    List<StudentManage> selectByPage(@Param("rows") int rows, @Param("page") int page, @Param("condition") String condition);

    /**
     * 按条件查询student_manage表中数据行数
     * 因为涉及了多表所以这里也需要多表联查不然无法设置条件
     * @param condition 条件参数
     * @return 数据行数
     */
    @Select("select count(*) from student_manage cm " +
            "join class c " +
            "on cm.class_id=c.class_id " +
            "join school sm " +
            "on c.school_id=sm.school_id " +
            "join record re " +
            "on re.record_id=cm.record_id  where 1=1 ${condition}")
    int selectCount(@Param("condition") String condition);

    /**
     *根据学员信息修改其数据
     * @param record 学员对象
     * @return 影响的行数(返回1则成功)
     */
    @Update({
        "update student_manage",
        "set student_name = #{studentName,jdbcType=VARCHAR},",
          "student_age = #{studentAge,jdbcType=INTEGER},",
          "student_sex = #{studentSex,jdbcType=VARCHAR},",
          "student_stat = #{studentStat,jdbcType=VARCHAR},",
          "student_address = #{studentAddress,jdbcType=VARCHAR},",
          "student_qq = #{studentQq,jdbcType=VARCHAR},",
          "student_phone = #{studentPhone,jdbcType=VARCHAR},",
          "student_date = #{studentDate,jdbcType=VARCHAR},",
          "class_id = #{classId,jdbcType=INTEGER},",
          "school_id = #{schoolId,jdbcType=INTEGER},",
          "record_id = #{recordId,jdbcType=INTEGER}",
        "where student_id = #{studentId,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(StudentManage record);


    //以下是获取下拉框数据的方法

    /**
     * 得到所有校区名称信息
     * @return 校区名称集合
     */
    @Select("select school_name from school")
    @Results({
            @Result(id = true,column = "school_name",property = "schoolName")
    })
    List<School> selectAllGetSchoolName();

    /**
     * 得到所有教师名称信息
     * @return 教师名称集合
     */
    @Select("select record_name from record")
    @Results({
            @Result(id = true,column = "record_name",property = "recordName")
    })
    List<Record> selectAllGetRecordName();

    /**
     * 得到所有班级名称信息
     * @return 校区名称集合
     */
    @Select("select class_name from class")
    @Results({
            @Result(id = true,column = "class_name",property = "className")
    })
    List<Class> selectAllGetClassName();

    /**
     *针对学员表(student_manage)进行操作
     * 根据学员名称查找其编号
     * @param studentName 学员名称
     * @return 影响的行数(>1:成功)
     */
    @Select("select student_id from student_manage where student_name=#{studentName}")
    int selectByStudentName(@Param("studentName") String studentName);

    /**
     *针对校区表(school)进行操作
     * 根据校区名称查找其编号
     * @param schoolName 校区名称
     * @return 影响的行数(>1:成功)
     */
    @Select("select school_id from school where school_name=#{schoolName}")
    int selectBySchoolName(@Param("schoolName") String schoolName);

    /**
     *针对班级表(class)进行操作
     * 根据班级名称查找其编号
     * @param className 班级名称
     * @return 影响的行数(>1:成功)
     */
    @Select("select class_id from class where class_name=#{className}")
    int selectByClassName(@Param("className") String className);

    /**
     *针对教师表(record)进行操作
     * 根据教师名称查找其编号
     * @param recordName 教师名称
     * @return 影响的行数(>1:成功)
     */
    @Select("select record_id from record where record_name=#{recordName}")
    int selectByRecordName(@Param("recordName") String recordName);

}